﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using FFS.Model;
using FFS.IDAL;
using FFS.DBUtility;

namespace FFS.SQLDAL
{
    public class SysRemindDAO:ISysRemindDAO
    {
        private const string SYS_REMIND_ID = "@SysRemindID";
        private const string REMIND_TYPE = "@RemindType";
        private const string REMIND_TITILE = "@RemindTitle";
        private const string REMIND_CONTENT = "@RemindContent";
        private const string REMIND_START_TIME = "@RemindStartTime";
        private const string REMIND_END_TIME = "@RemindEndTime";
        private const string IS_ACTIVE = "@IsActive";
        private const string IS_REMINDED = "@IsReminded";
        private const string INPUT_USER = "@input_user";

        private const string SELECT_ALL_SYS_REMIND = @"SELECT [SysRemindID]
                                                              ,[RemindType]
                                                              ,[RemindTitle]
                                                              ,[RemindContent]
                                                              ,[RemindStartTime]
                                                              ,[RemindEndTime]
                                                              ,[IsReminded]
                                                              ,[IsActive]
                                                              ,[is_deleted]
                                                              ,[input_date]
                                                              ,[input_user]
                                                          FROM [dbo].[SysRemind] 
                                                          where 1=1 
                                                            and  is_deleted = 0 ";
        private const string INSERT_REMIND = @"INSERT INTO [dbo].[SysRemind]
                                                           ([RemindType]
                                                           ,[RemindTitle]
                                                           ,[RemindContent]
                                                           ,[RemindStartTime]
                                                           ,[RemindEndTime]
                                                           ,[IsReminded]
                                                           ,[IsActive]
                                                           ,[is_deleted]
                                                           ,[input_date]
                                                           ,[input_user]) 
                                               values (@RemindType
                                                       ,@RemindTitle
                                                       ,@RemindContent
                                                       ,@RemindStartTime
                                                       ,@RemindEndTime
                                                       ,@IsReminded
                                                       ,@IsActive
                                                       ,0
                                                       ,getdate() 
                                                       ,@input_user)  

                                               select isnull(@@identity,0) as SysRemindID 
 
                                               ";
        private const string  UPDATE_REMIND = @"
                                                   UPDATE [dbo].[SysRemind]
                                                       SET [RemindType] = @RemindType
                                                          ,[RemindTitle] = @RemindTitle
                                                          ,[RemindContent] = @RemindContent
                                                          ,[RemindStartTime] = @RemindStartTime
                                                          ,[RemindEndTime] = @RemindEndTime
                                                          ,[IsReminded] = @IsReminded
                                                          ,[IsActive] = @IsActive
                                                          ,[input_date] = getdate() 
                                                          ,[input_user] = @input_user
                                                     WHERE SysRemindID = @SysRemindID 
                                                       AND is_deleted = 0 
                                                ";
        private const string DELETE_REMIND = @"

                                                    UPDATE  dbo.SysRemind 
                                                        set is_deleted = 1 ,input_date = getdate(),input_user = @input_user 
                                                     where  SysRemindID = @SysRemindID 
                                                     and    is_deleted = 0 
                                               ";

        /// <summary>
        /// 查询提醒信息的统一调用函数
        /// </summary>
        /// <returns></returns>
        private List<SysRemindInfo> GetQueryList(CommandType cmdType,string sqlText,params SqlParameter[] param)
        {
            List<SysRemindInfo> remindList = new List<SysRemindInfo>();
            using (SqlDataReader reader = DBHelper.ExecuteReader(cmdType,sqlText,param))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        SysRemindInfo remind = new SysRemindInfo();
                        remind.SysRemindID = reader.GetInt32(0);
                        remind.RemindType = reader.GetString(1);
                        remind.RemindTitle = reader.GetString(2);
                        remind.RemindContent = reader.GetString(3);
                        remind.RemindStartTime = reader.GetDateTime(4);
                        remind.RemindEndTime = reader.GetDateTime(5);
                        remind.IsActive = reader.GetBoolean(6);
                        remind.IsReminded = reader.GetBoolean(7);
                        remindList.Add(remind);
                    }
                }
            }

            return remindList;
        }


        #region ISysRemindDAO 成员

        public List<SysRemindInfo> GetAllSysRemind()
        {
            return this.GetQueryList(CommandType.Text,SELECT_ALL_SYS_REMIND,null);
        }

        public List<SysRemindInfo> GetSysRemindBySearchContent(string searchContent)
        {
            string sqlText = SELECT_ALL_SYS_REMIND + " and " + searchContent + " order by IsReminded,RemindStartTime asc ";
            return this.GetQueryList(CommandType.Text,sqlText,null);
        }
        public List<SysRemindInfo> GetSysRemindByRemindID(int remindID)
        {
            string sqlText = SELECT_ALL_SYS_REMIND + " and SysRemindID = @SysRemindID ";
            return this.GetQueryList(CommandType.Text,sqlText,new SqlParameter(SYS_REMIND_ID,remindID));
        }


        public int CreateSysRemind(SysRemindInfo remind)
        {
            object remind_id = null;
            SqlParameter[] param = new SqlParameter[8];
            param[0] = new SqlParameter(REMIND_TYPE,remind.RemindType);
            param[1] = new SqlParameter(REMIND_TITILE,remind.RemindTitle);
            param[2] = new SqlParameter(REMIND_CONTENT,remind.RemindContent);
            param[3] = new SqlParameter(REMIND_START_TIME,remind.RemindStartTime);
            param[4] = new SqlParameter(REMIND_END_TIME,remind.RemindEndTime);
            param[5] = new SqlParameter(IS_REMINDED,remind.IsReminded);
            param[6] = new SqlParameter(IS_ACTIVE,remind.IsActive);
            param[7] = new SqlParameter(INPUT_USER,remind.Input_User);

            //return DBHelper.ExecuteNonQuery(CommandType.Text,INSERT_REMIND,param);
            remind_id = DBHelper.ExecuteScalar(CommandType.Text, INSERT_REMIND, param);

            if (remind_id != null)
            {
                return int.Parse(remind_id.ToString());
            }
            else
            {
                return 0;
            }
            
        }

        public int ModifySysRemind(SysRemindInfo remind)
        {
            SqlParameter[] param = new SqlParameter[9];
            param[0] = new SqlParameter(REMIND_TYPE, remind.RemindType);
            param[1] = new SqlParameter(REMIND_TITILE, remind.RemindTitle);
            param[2] = new SqlParameter(REMIND_CONTENT, remind.RemindContent);
            param[3] = new SqlParameter(REMIND_START_TIME, remind.RemindStartTime);
            param[4] = new SqlParameter(REMIND_END_TIME, remind.RemindEndTime);
            param[5] = new SqlParameter(IS_REMINDED, remind.IsReminded);
            param[6] = new SqlParameter(IS_ACTIVE, remind.IsActive);
            param[7] = new SqlParameter(INPUT_USER, remind.Input_User);
            param[8] = new SqlParameter(SYS_REMIND_ID,remind.SysRemindID);

            return DBHelper.ExecuteNonQuery(CommandType.Text, UPDATE_REMIND, param);
        }

        public int DeleteSysRemind(SysRemindInfo remind)
        {
            SqlParameter[] param = {
                                    new SqlParameter(SYS_REMIND_ID,remind.SysRemindID),
                                    new SqlParameter(INPUT_USER,remind.Input_User)
                                   };
            return DBHelper.ExecuteNonQuery(CommandType.Text, DELETE_REMIND, param);
        }

        public int UpdateSysRemindStatus(int remindID,bool isReminded,string input_user)
        {
            string sqlText = @"  update SysRemind 
                                     set IsReminded = @IsReminded ,input_date = getdate(),input_user = @input_user 
                                  where SysRemindID = @SysRemindID and is_deleted = 0 
                                ";
            SqlParameter[] param = {
                                     new SqlParameter(IS_REMINDED,isReminded),
                                     new SqlParameter(INPUT_USER,input_user),
                                     new SqlParameter(SYS_REMIND_ID,remindID)
                                   };
            return DBHelper.ExecuteNonQuery(CommandType.Text,sqlText,param);
        }

        public List<SysRemindInfo> GetCurrentSysRemind(DateTime remindTime)
        {
            string sqlText = SELECT_ALL_SYS_REMIND + " and IsReminded = 0 and cast('" + remindTime.ToString().Trim() + "' as datetime)  between [RemindStartTime] and [RemindEndTime]  order by RemindStartTime asc ";
            return this.GetQueryList(CommandType.Text,sqlText,null);
        }

        #endregion
    }
}
